package org.smart4j.chapter2.helper;

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.smart4j.chapter2.util.CollectionUtil;
import org.smart4j.chapter2.util.PropUtil;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;

/**
 * Created by Li on 2017/7/18.
 */
public final class DatabaseHelper {

    private static final Logger LOGGER = LoggerFactory.getLogger(DatabaseHelper.class);

//    private static final String DRIVER;
//    private static final String URL;
//    private static final String USERNAME;
//    private static final String PASSWORD;

    private static final ThreadLocal<Connection> CONNECTION_HOLDER;
    private static final QueryRunner QUERY_RUNNER;

    private static final BasicDataSource DATA_SOURCE;

    static {

        CONNECTION_HOLDER = new ThreadLocal<Connection>();
        QUERY_RUNNER = new QueryRunner();

        Properties conf = PropUtil.loadProps("config.properties");
//        DRIVER = conf.getProperty("jdbc.driver");
//        URL = conf.getProperty("jdbc.url");
//        USERNAME = conf.getProperty("jdbc.username");
//        PASSWORD = conf.getProperty("jdbc.password");

        String driver = conf.getProperty("jdbc.driver");
        String url = conf.getProperty("jdbc.url");
        String username = conf.getProperty("jdbc.username");
        String password = conf.getProperty("jdbc.password");

        DATA_SOURCE = new BasicDataSource();
        DATA_SOURCE.setDriverClassName(driver);
        DATA_SOURCE.setUrl(url);
        DATA_SOURCE.setUsername(username);
        DATA_SOURCE.setPassword(password);

//        try {
//            Class.forName(DRIVER);
//        } catch (ClassNotFoundException e) {
//            e.printStackTrace();
//            LOGGER.error("can not load jdbc driver", e);
//        }
    }

    /*连接数据库*/
    public static Connection getConnection() {
        Connection conn = CONNECTION_HOLDER.get();
        if (conn == null) {
            try {
                conn = DATA_SOURCE.getConnection();
            } catch (SQLException e) {
                LOGGER.error("get connection failure", e);
                throw new RuntimeException(e);
            } finally {
                CONNECTION_HOLDER.set(conn);
            }
        }

        return conn;
    }

    /*关闭数据库*/
//    public static void closeConnection() {
//
//        Connection conn = CONNECTION_HOLDER.get();
//        if (conn != null) {
//            try {
//                conn.close();
//            } catch (SQLException e) {
//                LOGGER.error("close connection failure", e);
//            } finally {
//                CONNECTION_HOLDER.remove();
//            }
//        }
//    }


    /*查询实体列表*/
    public static <T> List<T> queryEntityList(Class<T> entityClass, Object... params) {

        StringBuilder sql = new StringBuilder("SELECT * FROM");
        sql.append(" " + entityClass.getSimpleName());
        List<T> entityList;
        try {
            Connection conn = getConnection();
            entityList = QUERY_RUNNER.query(conn, sql.toString(), new BeanListHandler<T>(entityClass), params);
        } catch (SQLException e) {
            LOGGER.error("query entity list failure", e);
            throw new RuntimeException(e);
        }
        return entityList;
    }

    /*查询实体*/
    public static <T> T queryEntity(Class<T> entityClass, long id, Object... params) {
        T entity;

        StringBuilder sql = new StringBuilder("SELECT * FROM ");
        sql.append(getTableName(entityClass));
        sql.append(" where id = ");
        sql.append(id);
        try {
            Connection conn = getConnection();
            entity = QUERY_RUNNER.query(conn, sql.toString(), new BeanHandler<T>(entityClass), params);
        } catch (SQLException e) {
            LOGGER.error("query entity failure", e);
            throw new RuntimeException(e);
        }
        return entity;
    }


    /*插入实体*/
    public static <T> boolean insertEntity(Class<T> entityClass, Map<String, Object> fileMap) {
        if (CollectionUtil.isEmpty(fileMap)) {
            LOGGER.error("can not insert entity: fileMap is empty");
            return false;
        }
        String sql = "insert " + getTableName(entityClass);
        StringBuilder columns = new StringBuilder("(");
        StringBuilder values = new StringBuilder("(");
        for (String fieldName : fileMap.keySet()) {
            columns.append(fieldName).append(", ");
            values.append("?, ");
        }
        columns.replace(columns.lastIndexOf(", "), columns.length(), ")");
        values.replace(values.lastIndexOf(", "), values.length(), ")");
        sql += columns + " values " + values;
        Object[] params = fileMap.values().toArray();
        return executeUpdate(sql, params) == 1;

    }

    /*更新实体*/
    public static <T> boolean updateEntity(Class<T> entityClass, long id, Map<String, Object> fileMap) {

        if (CollectionUtil.isEmpty(fileMap)) {
            LOGGER.error("can not update entity: fileMap is empty");
            return false;
        }

        String sql = "update " + getTableName(entityClass) + " SET ";
        StringBuilder columns = new StringBuilder();
        for (String fieldName : fileMap.keySet()) {
            columns.append(fieldName).append("=?, ");
        }
        sql += columns.substring(0, columns.lastIndexOf(", ")) + " where id= ?";

        List<Object> paramList = new ArrayList<Object>();
        paramList.addAll(fileMap.values());
        paramList.add(id);
        Object[] params = paramList.toArray();


        return executeUpdate(sql, params) == 1;
    }

    /*删除实体*/
    public static <T> boolean deleteEntity(Class<T> entityClass, long id) {
        String sql = "delete from " + getTableName(entityClass) + " where id = ?";
        return executeUpdate(sql, id) == 1;
    }


    /*执行更新语句,返回执行后受影响的行数*/
    public static int executeUpdate(String sql, Object... params) {
        int rows = 0;
        Connection conn = getConnection();
        try {
            rows = QUERY_RUNNER.update(conn, sql, params);
        } catch (SQLException e) {
            LOGGER.error("execute update failure ", e);
            throw new RuntimeException(e);
        }
        return rows;
    }

    private static String getTableName(Class<?> entityClass) {
        return entityClass.getSimpleName();
    }


}
